﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using VswService_V7.ClientCommand;
using VswService_V7.Utility;

namespace VswService_V7.DataBase.Tables
{
    public class USER_TABLE
    {
        public USER_TABLE()
        {

        }
        public USER_TABLE(DataTable user)
        {
            UserTable = user;
        }
		public static int pageNum = 20;
		#region  field
		public static readonly string defaultPassword = "123";
        public static readonly string tableName = "USER_TABLE";
        public static readonly string fd_userNo = "USER_NO";
        public static readonly string fd_userName = "USER_NAME";
		public static readonly string fd_userPassword = "USER_PASSWORD";
		public static readonly string fd_userLevel = "USER_LEVEL";		
		public static readonly string fd_createDate = "CREATE_DATE";
        public static readonly string fd_userDepartment = "USER_DEPARTMENT";
        public static readonly string fd_shiftName = "SHIFT_NAME";
		public static readonly string fd_updateTime = "UPDATE_TIME";


		public DataTable UserTable
        {
            get;
            set;
        }


        #endregion

        #region private property



        #endregion
        /// <summary>
        /// user login
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        public static DataTable[] Login(string userName, string password)
        {
            try
            {
                string usersql = string.Format("select * from {0} where {1}='{2}' and {3}='{4}'",
                    tableName, fd_userNo, userName, fd_userPassword, password);

                string shiftsql = string.Format("select * from {0}",
                    SHIFT_TABLE.tableName);

                string teamsql = string.Format("select * from {0}",
                    TEAM_TABLE.tableName);
                DataTable userTable = OracleHelper.Exct_QueryTable(usersql);
                DataTable shiftTable = OracleHelper.Exct_QueryTable(shiftsql);
                DataTable teamTable = OracleHelper.Exct_QueryTable(teamsql);
                if (userTable != null && userTable.Rows.Count>0)
                {
                    DataTable[] tables = new DataTable[] { userTable, shiftTable,teamTable };
                    return tables;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        /// <summary>
        /// pagination query user information
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="index"></param>
        /// <param name="num_page"></param>
        /// <returns></returns>
        public static DataTable SelectUSER_BYINDEX(int minRow, int maxRow,out string totalPages)
        {
			try
			{
				string sqlUser = string.Format("select * from (select a.*,ROWNUM rn from {0} a order by {1} asc,{2} asc) where rn>{3} and rn<={4}",
				  tableName, fd_updateTime, fd_userNo, minRow, maxRow);
				string sqlUserCount = string.Format("select count(*) from {0}",
				  tableName);
				DataTable userTable = OracleHelper.Exct_QueryTable(sqlUser);
				if (userTable != null)
				{
					int userPages = Convert.ToInt32(TASK_INFO.GetTableCount(sqlUserCount));
					if (userPages % pageNum == 0)
					{
						totalPages = (userPages / pageNum).ToString();
					}
					else
					{
						totalPages = (userPages / pageNum + 1).ToString();
					}
					LogHelper.WriteLog("SelectUSER_BYINDEX Success");
					return userTable;
				}
				else
				{
					totalPages = "";
					return null; ;
				}

			}
			catch (Exception ex)
			{
				LogHelper.WriteLog("SelectUSER_BYINDEX", ex);
				throw ex;
			}		
        }

        /// <summary>
        /// insert a user
        /// </summary>
        /// <param name="content"></param>
        /// <returns></returns>
        public static DataTable Insert_USER(string userid, string username, string userlevel, string creatdate, string department, string shift, string password,out string totalPages)
        {
            try
            {				
				string sql = string.Format("insert into {0}({1},{2},{3},{4},{5},{6},{7}) " +
                    "values('{8}','{9}','{10}','{11}','{12}','{13}','{14}')",
                    tableName, fd_userNo, fd_userName, fd_userLevel, fd_createDate, fd_userDepartment, fd_shiftName, fd_userPassword,
                    userid, username, userlevel, creatdate, department, shift, password);
				int result = OracleHelper.Exct_NonQuery(sql);
				if (result > 0)
				{
					DataTable dataTable = SelectUSER_BYINDEXafterInsert(out totalPages);
					return dataTable;
				}
				else
				{
					totalPages = "";
					return null;
				}
            }
            catch (Exception ex)
            {
				LogHelper.WriteLog("Insert_USER", ex);
                throw ex;
            }
        }
		/// <summary>
		/// after inserting user,return user information of the first page
		/// </summary>
		/// <param name="minRow"></param>
		/// <param name="maxRow"></param>
		/// <param name="totalPages"></param>
		/// <returns></returns>
		public static DataTable SelectUSER_BYINDEXafterInsert(out string totalPages)
		{
			try
			{
				string sqlUserCount = string.Format("select count(*) from {0}",
				  tableName);
				int userPages = Convert.ToInt32(TASK_INFO.GetTableCount(sqlUserCount));
				if (userPages % pageNum == 0)
				{
					totalPages = (userPages / pageNum).ToString();
				}
				else
				{
					totalPages = (userPages / pageNum + 1).ToString();
				}

				string sqlUser = string.Format("select * from (select a.*,ROWNUM rn from {0} a order by {1} asc,{2} asc) where rn>{3} and rn<={4}",
				  tableName, fd_updateTime, fd_userNo, (Convert.ToInt32(totalPages)-1) * pageNum, Convert.ToInt32(totalPages)* pageNum);
				DataTable userTable = OracleHelper.Exct_QueryTable(sqlUser);
				return userTable;

			}
			catch (Exception ex)
			{
				LogHelper.WriteLog("SelectUSER_BYINDEX", ex);
				throw ex;
			}
		}

		/// <summary>
		/// update user information
		/// </summary>
		/// <param name="content"></param>
		/// <returns></returns>
		public static DataTable Update_USER(string userid,string username,string userlevel,string department,string shift)
        {
            try
            {
                string sqlUpdate = string.Format("update {0} set {2}='{7}',{3}='{8}',{4}='{9}',{5}='{10}' where {1}='{6}'",
                    tableName, fd_userNo, fd_userName, fd_userLevel, fd_userDepartment, fd_shiftName,
                    userid, username, userlevel, department, shift);
                int result = OracleHelper.Exct_NonQuery(sqlUpdate);
				if (result > 0)
				{
					string sql = string.Format("select * from {0} where {1}='{2}'",
						tableName, fd_userNo, userid);
					DataTable dataTable = OracleHelper.Exct_QueryTable(sql);
					return dataTable;
				}
				else
				{
					return null;
				}
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// delete user
        /// </summary>
        /// <param name="content"></param>
        /// <returns></returns>
        public static DataTable Delete_USER(string userNo,out string totalPages)
        {
            try
            {
                string sql = string.Format("delete from {0} where {1}='{2}'",
                    tableName, fd_userNo, userNo);
                int result = OracleHelper.Exct_NonQuery(sql);
				DataTable dataTable = SelectUSER_BYINDEX(0, pageNum, out totalPages);
				if (result > 0)
				{					
					return dataTable;
				}
				else
				{
					return null; ;
				}
            }
            catch (Exception ex)
            {
				LogHelper.WriteLog("Delete_USER", ex);
                throw ex;
            }
        }

        /// <summary>
        /// reset user password to 123
        /// </summary>
        /// <param name="content"></param>
        /// <returns></returns>
        public static int Reset_USERPASSWORD(string userid)
        {
            try
            {

                string sql = string.Format("update {0} set {2}='{4}' where {1}='{3}'",
                    tableName, fd_userNo, fd_userPassword, userid, defaultPassword);
                int result = OracleHelper.Exct_NonQuery(sql);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// update user password
        /// </summary>
        /// <param name="content"></param>
        /// <returns></returns>
        public static int Update_USERPASSWORD(string userid,string password)
        {
            try
            {               
                string sql = string.Format("update {0} set {2}='{4}' where {1}='{3}'",
                    tableName, fd_userNo, fd_userPassword, userid, password);
                int result = OracleHelper.Exct_NonQuery(sql);
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// query user information by userid
        /// </summary>
        /// <param name="content"></param>
        /// <returns></returns>
        public static DataTable Select_USERINFO(string userid)
        {
            try
            {               
                string usersql = string.Format("select * from {0} where {1}='{2}'", tableName, fd_userNo, userid);
                DataTable userTable = OracleHelper.Exct_QueryTable(usersql);
                return userTable;
            }

            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// create user table
        /// </summary>
        /// <param name="id"></param>
        /// <param name="name"></param>
        /// <param name="level"></param>
        /// <param name="date"></param>
        /// <param name="dep"></param>
        /// <param name="shift"></param>
        /// <param name="password"></param>
        /// <returns></returns>
        private static DataTable CreateUserTable(string id, string name, string level, string date, string dep, string shift, string password)
        {
            DataTable dt = new DataTable("user_table");
            DataColumn dc1 = new DataColumn("user_id", Type.GetType("System.String"));
            DataColumn dc2 = new DataColumn("user_name", Type.GetType("System.Single"));
            DataColumn dc3 = new DataColumn("user_level", Type.GetType("System.Single"));
            DataColumn dc4 = new DataColumn("create_Date", Type.GetType("System.String"));
            DataColumn dc5 = new DataColumn("department", Type.GetType("System.Single"));
            DataColumn dc6 = new DataColumn("user_shift", Type.GetType("System.Single"));
            DataColumn dc7 = new DataColumn("user_password", Type.GetType("System.Single"));
            dt.Columns.Add(dc1);
            dt.Columns.Add(dc2);
            dt.Columns.Add(dc3);
            dt.Columns.Add(dc4);
            dt.Columns.Add(dc5);
            dt.Columns.Add(dc6);
            dt.Columns.Add(dc7);

            DataRow dr = dt.NewRow();
            dr["user_id"] = id;
            dr["user_name"] = name;
            dr["user_level"] = level;
            dr["create_Date"] = date;
            dr["department"] = dep;
            dr["user_shift"] = shift;
            dr["user_password"] = password;
            dt.Rows.Add(dr);
            return dt;
        }
    }
}
